package tu.java.reservation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import tu.java.db.DBConnManager;
import tu.java.db.DBUtil;
import tu.java.login.User;

public class Reservation {
	
	
	
	private int				projectionId;
	private int				reservationId;
	private int				reservationState;
	private String			reservationInfo;
	private User			reservationUser;
	private Vector<Ticket>	tickets;
	
	
	
	public boolean addReservation(int userId) {
		boolean status = true;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnManager.getInstance().getConnection(false);
			pstmt = conn.prepareStatement("INSERT INTO TR01_RESERVATIONS(CR01_RESERVATION_INFO,CP01_PROJECTION_ID,CR01_STATE,CS02_USER_ID) VALUES (?,?,?,?)");
			pstmt.setString(1, reservationInfo);
			pstmt.setInt(2, projectionId);
			pstmt.setInt(3, reservationState);
			pstmt.setInt(4, userId);
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement("SELECT TR01_RESERVATIONS.CURRVAL FROM DUAL");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				reservationId = rs.getInt(1);
			}
			
			for (int i = 0; i < tickets.size(); i++) {
				Ticket ticket = tickets.get(i);
				ticket.addTicket(conn, pstmt, reservationId);
			}
		}
		catch (Exception e) {
			status = false;
			e.printStackTrace();
		}
		finally {
			DBUtil.commit(conn, status);
			DBConnManager.getInstance().close(rs);
			DBConnManager.getInstance().close(pstmt);
			DBConnManager.getInstance().freeConnection(conn);
		}
		return status;
	}
	
	
	public static Vector<Reservation> searchReservationsForProjection(int projectionId) {
		Vector<Reservation> reservations = new Vector<Reservation>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnManager.getInstance().getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT CR01_RESERVATION_ID,CR01_RESERVATION_INFO,CP01_PROJECTION_ID,CR01_STATE,CS02_USER_ID FROM TR01_RESERVATIONS");
			sql.append(" WHERE CP01_PROJECTION_ID=?");
			
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setInt(1, projectionId);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				Reservation r = new Reservation();
				r.reservationId = rs.getInt(1);
				r.reservationInfo = rs.getString(2);
				r.projectionId = rs.getInt(3);
				r.reservationState = rs.getInt(4);
				r.reservationUser = User.searchUser(rs.getInt(5));
				r.tickets = Ticket.searchTickets(r.reservationId);
				reservations.add(r);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DBConnManager.getInstance().close(rs);
			DBConnManager.getInstance().close(pstmt);
			DBConnManager.getInstance().freeConnection(conn);
		}
		return reservations;
	}
	
	
	public static Vector<Reservation> searchReservations(int reservationId, int userId) {
		Vector<Reservation> reservations = new Vector<Reservation>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnManager.getInstance().getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT CR01_RESERVATION_ID,CR01_RESERVATION_INFO,CP01_PROJECTION_ID,CR01_STATE,CS02_USER_ID FROM TR01_RESERVATIONS");
			if (reservationId != 0) {
				sql.append(" WHERE CR01_RESERVATION_ID=?");
			}
			else {
				sql.append(" WHERE CS02_USER_ID=?");
			}
			pstmt = conn.prepareStatement(sql.toString());
			if (reservationId != 0) {
				pstmt.setInt(1, reservationId);
			}
			if (userId != 0) {
				if (reservationId != 0) {
					pstmt.setInt(2, userId);
				}
				else {
					pstmt.setInt(1, userId);
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Reservation r = new Reservation();
				r.reservationId = rs.getInt(1);
				r.reservationInfo = rs.getString(2);
				r.projectionId = rs.getInt(3);
				r.reservationState = rs.getInt(4);
				r.reservationUser = User.searchUser(rs.getInt(5));
				r.tickets = Ticket.searchTickets(r.reservationId);
				reservations.add(r);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DBConnManager.getInstance().close(rs);
			DBConnManager.getInstance().close(pstmt);
			DBConnManager.getInstance().freeConnection(conn);
		}
		return reservations;
	}
	
	
	
	public boolean annulReservation() {
		boolean status = true;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnManager.getInstance().getConnection();
			pstmt = conn.prepareStatement(" DELETE FROM TR01_RESERVATIONS WHERE CR01_RESERVATION_ID=?");
			pstmt.setInt(1, reservationId);
			pstmt.executeUpdate();
		}
		catch (Exception e) {
			status = false;
			e.printStackTrace();
		}
		finally {
			DBConnManager.getInstance().close(rs);
			DBConnManager.getInstance().close(pstmt);
			DBConnManager.getInstance().freeConnection(conn);
		}
		return status;		
	}
	
	
	public boolean changeState() {
		boolean status = true;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnManager.getInstance().getConnection();
			pstmt = conn.prepareStatement(" UPDATE TR01_RESERVATIONS SET CR01_STATE=?");
			reservationState = reservationState == 0 ? 1 : 0;
			pstmt.setInt(1, reservationState);
			pstmt.executeUpdate();
		}
		catch (Exception e) {
			status = false;
			e.printStackTrace();
		}
		finally {
			DBConnManager.getInstance().close(rs);
			DBConnManager.getInstance().close(pstmt);
			DBConnManager.getInstance().freeConnection(conn);
		}
		return status;		
	}
	
	
	public int getProjectionId() {
		return projectionId;
	}
	
	
	
	public void setProjectionId(int projectionId) {
		this.projectionId = projectionId;
	}
	
	
	
	public Vector<Ticket> getTickets() {
		return tickets;
	}
	
	
	
	public void setTickets(Vector<Ticket> tickets) {
		this.tickets = tickets;
	}
	
	
	
	public int getReservationId() {
		return reservationId;
	}
	
	
	
	public void setReservationId(int reservationId) {
		this.reservationId = reservationId;
	}
	
	
	
	public String getReservationInfo() {
		return reservationInfo;
	}
	
	
	
	public void setReservationInfo(String reservationInfo) {
		this.reservationInfo = reservationInfo;
	}



	public int getReservationState() {
		return reservationState;
	}



	public void setReservationState(int reservationState) {
		this.reservationState = reservationState;
	}



	public User getReservationUser() {
		return reservationUser;
	}



	public void setReservationUser(User reservationUser) {
		this.reservationUser = reservationUser;
	}
	
}
